<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2010-11-25
 * Time: 17:56:57
 * To change this template use File | Settings | File Templates.
 */
 
class otherComponents  extends sfComponents
{
    public $dbconn;

    public function executeSurvey()
    {
        //$dbconn = $this->GetDBConn();
        $sqlStr="select top 1 cast(question as varchar(1000)) question, online_survey_id as id from onlineSurvey order by is_on_top_b desc, create_time desc ";
        $result = $this->getDataRows($sqlStr);
        $this->row_array = $result[0];
    }

    public function executeQuestions()
    {
        //$dbconn = $this->GetDBConn();
        if($this->recordCount == null)
            $this->recordCount = 20;
        $sqlStr = "select top 1 cast(name as varchar(200)) name,cast(title as varchar(2000)) title, cast(snapshot_big as varchar(200)) snapshot_big,question_content_id as id from celebrity c inner join questioncontent q on c.celebrity_id = q.celebrity_id  order by q.is_on_top_b desc, q.create_time desc";
        $this->row_array = $this->getDataRows($sqlStr);
        $this->row_array = $this->row_array[0];
        //odbc_free_result($result);
        $sqlStr = "select top ".$this->recordCount." cast(title as varchar(1000)) title from question where question_content_id=".$this->row_array["id"]." order by question_id desc";
        //$result = odbc_exec($dbconn,$sqlStr);
        $result = $this->getDataRows($sqlStr);
        $questionLi="";
        foreach($result as $row_array)
        {
             $questionLi = $questionLi."<li>".$row_array["title"]."</li>";
        }
        $this->questionLi= $questionLi;
    }

    public function executeStarpk()
    {
        //$dbconn = $this->GetDBConn();
        $sqlStr = "select top 1 celebrity_pk_id, celebrity1_id, celebrity2_id, case when count1_i is null then 0 else count1_i end count1_i, case when count2_i is null then 0 else count2_i end count2_i from CelebrityPK where is_alive_b = 1 order by is_on_top_b desc, create_time desc";
        //$result = odbc_exec($dbconn,$sqlStr);
        $result_array = $this->getDataRows($sqlStr);
        $result_array = $result_array[0];
        $this->pkArray = array();

        if($result_array != null)
        {
            $temparray = array();
            $this->pkId = $result_array["celebrity_pk_id"];
            $temparray[0] = array("id" => $result_array["celebrity1_id"], "count"=>$result_array["count1_i"]);
            $temparray[1] = array("id" => $result_array["celebrity2_id"], "count"=>$result_array["count2_i"]);
            //odbc_free_result($result);
            $sqlStr = "select celebrity_id,cast(snapshot_big as varchar(200)) imgUrl from Celebrity where celebrity_id in (".$result_array["celebrity1_id"].",".$result_array["celebrity2_id"].")";
            $result = $this->getDataRows($sqlStr);
            //$result = odbc_exec($dbconn,$sqlStr);
            foreach($result as $result_array)
            {
                if($result_array["celebrity_id"] == $temparray[0]["id"])
                    $this->pkArray[0] = array("id" =>$result_array["celebrity_id"],"imgUrl"=>$result_array["imgUrl"],"count"=>$temparray[0]["count"]);
                else
                    $this->pkArray[1] = array("id" =>$result_array["celebrity_id"],"imgUrl"=>$result_array["imgUrl"],"count"=>$temparray[1]["count"]);
            }
        }



    }

    public function executeUpdateStarpk()
    {
        //$dbconn = $this->GetDBConn();
        $sqlStr = "update CelebrityPK set ";
        if($this->id != null)
        {
            if($this->flag != null)
            {
                if($this->flag == "1")
                    $sqlStr = $sqlStr." count1_i = case when count1_i is null then 0 else count1_i end + 1 ";
                else
                    $sqlStr = $sqlStr." count2_i = case when count2_i is null then 0 else count2_i end + 1 ";
                $sqlStr = $sqlStr." where celebrity_pk_id=".$this->id;
                $this->update($sqlStr);

                $sqlStr ="select (case when count1_i is null then 0 else count1_i end) count1_i, (case when count2_i is null then 0 else count2_i end) count2_i from CelebrityPK where celebrity_pk_id=".$this->id;
                $result = $this->getDataRows($sqlStr);
                $this->row_array = $result[0];
            }
        }
    }

    public function executeGetQuestions()
    {
        if($this->id != null)
        {
            //$dbconn = $this->GetDBConn();
            if($this->question != null)
            {
                $sqlStr = "insert into question(question_content_id,title,create_time) values(".$this->id.",'".$this->question."',getdate());";
                $this->update($sqlStr);
                //odbc_exec($dbconn,$sqlStr);
            }
            $sqlStr="select cast(title as varchar(1000)) title from question where question_content_id=".$this->id;
            $sqlStr = $sqlStr." order by question_id desc";
            $result = $this->getDataRows($sqlStr);
            $questionLi="";
            foreach($result as $row_array)
            {
                $questionLi = $questionLi."<li>".$row_array["title"]."</li>";
            }
            $this->questionLi= $questionLi;
        }
    }

     public function executeUpdateSurvey()
    {
        //$dbconn = $this->GetDBConn();
        $sqlStr = "update onlineSurvey set ";
        if($this->flag != null)
        {
            if($this->flag == "yes")
                $sqlStr = $sqlStr."yes_count_i = case when yes_count_i is null then 0 else yes_count_i end + 1";
            else
                $sqlStr = $sqlStr."no_count_i = case when no_count_i is null then 0 else no_count_i end  + 1";
            $sqlStr = $sqlStr." where online_survey_id = ".$this->id;
            $result = $this->update($sqlStr);
        }
        $sqlStr ="select (case when yes_count_i is null then 0 else yes_count_i end) yes_count, (case when no_count_i is null then 0 else no_count_i end) no_count from OnlineSurvey where online_survey_id = ".$this->id;
        $result = $this->getDataRows($sqlStr);
        $this->row_array = $result[0];
        
    }

    public function executeColligatebox()
    {

    }

    public function executeMoviebox()
    {
        $this->boxRecords=@Doctrine_Core::getTable('boxrecord')->createQuery()->limit(4)->orderBy('box_record_id desc')->execute();
        $a = $this->boxRecords[0];
        $this->boxRecords[0] = $this->boxRecords[1];
        $this->boxRecords[1] = $a;
    }

    public function executeMusicbox()
    {        
        $this->boxRecords=@Doctrine_Core::getTable('boxrecord')->createQuery()->limit(4)->execute();
    }

    public function executeAdvertise()
    {
        if('top'!=$this->spase)
           $this->spase='right';
        $orderi=mt_rand(1,5);
        $this->advertise=@Doctrine_Query::create()->from('Advertise')->where("name='".$this->spase."'")->andWhere('order_i='.$orderi)->fetchOne('name');
    }

    function getDataRows($sqlStr)
    {
        $dbBase = new DbBase();
        return $dbBase->getDataQuery($sqlStr);
    }

    function update($sqlStr)
    {
        $dbBase = new DbBase();
        return $dbBase->update($sqlStr);
    }
}
